• Array Builder
• What does it do?
• How to Access it?
• VBA: Intro to Arrays, Dictionaries, and Collections
• Array Builder Walkthrough
• Arrays
• Define the Array
• Populate the Array
• Code For Working With Arrays
• Collections & Dictionaries
• Define the Collections & Dictionaries
• Populate the Collection & Dictionaries
• Code For Working With Collections & Dictionaries

Array Builder

What does it do?

Use the Array Builder to create Arrays, Dictionaries, and Collections and manipulate them.

How to Access it?

VBA: Intro to Arrays, Dictionaries, and Collections

Arrays, dictionaries, and collections are used to store information. Dictionaries and collections are like lists of data that can have a key. For example, you create a list of all items a store sells and the # of items in the store's inventory. The item name (ex. socks) would be the key and the # of items would be the value.

Think of Arrays like ranges of cells in an Excel spreadsheet. They can be any size and you can access values by referencing their positions in the array (ex. arr(1,4)), just like cells (ex. cells(1,4)).

Automate Excel has a brief introduction to Arrays here.

Array Builder Walkthrough

Arrays

Define the Array

1. Name - Name the array
2. Value Type - What type of values (integer, text, etc.) will the array store? Variant (default) arrays accept all values.
3. Declare Type - Static arrays can not change size. Dynamic arrays can change size.
4. Populate - Do you want to use the Array Builder to populate your array?
   1. Read Excel Range - Will program VBA to populate the array from a range of cells each time the code is ran. Use this option when the Excel range will always be used to populate the array.
   2. Read Excel Range and Hard-code Values - Tells the Array Builder to read a range of cells, and hard-code those values into the VBA code. Use this option to save time when populating the array.
   3. Do not populate - Only create the array, do not populate it yet.

Populate the Array

1. Range - Define the Excel Range to populate the array (not needed if Do Not Populate is selected).
2. Dimensions and Size - Unless Do Not Populate is selected, these values will populate automatically.
   1. Dimension decides whether the array is 1-dimensional or 2-dimensional. 1-dimensional is just a list. 2-dimensional is like a range of cells.
   2. Lower and Upper Bound determines the size of the array. How many items will it contain? The Lower Bound indicates the starting value. It can be 0,1, or whatever you'd like.

Code For Working With Arrays

These buttons generate code to work with your newly created array. Mouse over the button to see the generated code.

Collections & Dictionaries

The Collections and Dictionaries Builders have the same settings.

Define the Collections & Dictionaries

1. Name - Name the collection
2. Populate - Do you want to use the Collection Builder to populate your collection?
   1. Read Excel Range - Will program VBA to populate the collection from a range of cells each time the code is ran. Use this option when the Excel range will always be used to populate the collection.
   2. Read Excel Range and Hard-code Values - Tells the Collection Builder to read a range of cells, and hard-code those values into the VBA code. Use this option to save time when populating the collection.
   3. Do not populate - Only create the collection, do not populate it yet.

Populate the Collection & Dictionaries

1. Range - Define the Excel Range to populate the array (not needed if Do Not Populate is selected).

Code For Working With Collections & Dictionaries

Code For Working With Collections

Code For Working With Dictionaries

These buttons generate code to work with your newly created collection. Mouse over the button to see the generated code.

Last modified 3 years ago.